//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CommunityServer.Components;
using CommunityServer.Files;
using CommunityServer.Files.Components;
using CommunityServer.SqlDataProvider;

namespace CommunityServer.Data
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	public class FileGallerySqlDataProvider : FileGalleryDataProvider
	{
		#region Member variables
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#endregion

		#region Constructor
		/****************************************************************
		// SqlDataProvider
		//
		/// <summary>
		/// Class constructor
		/// </summary>
		//
		****************************************************************/
		public FileGallerySqlDataProvider(string databaseOwner, string connectionString) 
		{

			// Read the connection string for this provider
			//
			this.connectionString = connectionString;

			// Read the database owner name for this provider
			//
			this.databaseOwner = databaseOwner;

			sqlHelper = ProviderHelper.Instance();

		}
		#endregion
	
		#region SettingsID 
		protected int GetSettingsID()
		{
			return  sqlHelper.GetSettingsID();
		}
		protected SqlParameter SettingsIDParameter()
		{
			SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
			p.Value = GetSettingsID();
			return p;
		}
		#endregion

		#region Helpers
		protected SqlConnection GetSqlConnection () 
		{

			try 
			{
				return new SqlConnection(ConnectionString);
			} 
			catch 
			{
				throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid.");
			}

		}

		public string ConnectionString 
		{
			get 
			{
				return connectionString;
			}
			set 
			{
				connectionString = value;
			}
		}
		#endregion

		public override int GetRandomSectionID()
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_GetRandomSectionID", myConnection);
				int sectionID;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = ApplicationType.FileGallery;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if(!dr.Read())
				{
					dr.Close();
					myConnection.Close();
					return -1;
				}

				sectionID = (int) dr["SectionID"];

				dr.Close();
				myConnection.Close();

				// we have a post to work with  
				return sectionID;
			}
		}

		public override UserDownloadSet GetUserDownloads(int postID, DateTime beginDate, DateTime endDate, int pageIndex, int pageSize)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".files_users_WhoDownloadedFile", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = beginDate;
				myCommand.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				// Get the requested forums
				UserDownloadSet uds = new UserDownloadSet();
				while (dr.Read()) 
				{
					UserDownload ud = new UserDownload();
					PopulateUserDownloadFromIDataReader(dr, ref ud);
					uds.UserDownloads.Add(ud);
				}

				dr.NextResult();
				
				if (dr.Read())
					uds.TotalRecords = (int) dr[0];
           
				// Done with the reader and the connection
				dr.Close();
				myConnection.Close();

				return uds;
			}		
		}

		public override Hashtable GetFolders(bool mergePermissions)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Sections_Get", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				Hashtable folders = new Hashtable();

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.FileGallery;
				myCommand.Parameters.Add("@AllowInactive", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				// Get the requested forums
				while (dr.Read()) 
				{
					Folder folder = new Folder();
					PopulateFolderFromIDataReader(dr, ref folder);

					// Add the album to the Hashtable
					folders.Add(folder.ApplicationKey.ToLower(), folder);
				}

				// Get the permissions
				if (dr.NextResult()) 
				{
					while (dr.Read()) 
					{
						// Get the section id
						FileGalleryPermission permission = new FileGalleryPermission();
						CommonDataProvider.PopulatePermissionFromIDataReader(permission, dr);

						// Are we applying this to a particular forum?
						if(dr["ApplicationKey"] != DBNull.Value)
						{
							string applicationKey = (string) dr["ApplicationKey"];
							Folder folder = folders[applicationKey] as Folder;

							if(folder == null)
								continue;

							permission.SectionID = folder.SectionID;
							folder.PermissionSet.Add( permission.Name, permission );
						}
						else
						{
							// Apply the permission to all forums
							foreach(Folder folder in folders.Values)
							{
								permission.SectionID = folder.SectionID;

								// Merge the permission
								if(folder.PermissionSet[permission.Name] == null)
									folder.PermissionSet.Add( permission.Name, permission );
							}
						}
					}
				}
            
				// Done with the reader and the connection
				dr.Close();
				myConnection.Close();

				return folders;
			}
		}

		public override void UpdateEntryCategories(int folderID, int postID, string[] categoryList)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_UpdatePostsInCategories", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = folderID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@CategoryList", SqlDbType.NVarChar, 4000).Value = ConvertCategoriesToXML(categoryList);
				myCommand.Parameters.Add(this.SettingsIDParameter());
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ArrayList GetEntryCategories(int postID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Posts_GetPostInCategories", myConnection);
				ArrayList categories = new ArrayList();

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				while(dr.Read())
				{
					// Add categories
					PostCategory category = new PostCategory();

					category.CategoryID =			(int) dr["CategoryID"];
					category.SectionID =				(int) dr["SectionID"];
					category.Name =					(string) dr["Name"];
					category.IsEnabled =			(bool) dr["IsEnabled"];
					category.ParentID =				(int) dr["ParentID"];

					if(dr["Description"] != DBNull.Value)
						category.Description =		(string) dr["Description"];

					categories.Add(category);
				}

				dr.Close();
				myConnection.Close();

				// return the collection of IDs
				return categories;
			}
		}

		public override void CreateEntry(Entry entry)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = entry.SectionID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = entry.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = 0;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = entry.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = entry.IsLocked;
				myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = entry.PostType;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = entry.PostMedia;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = entry.EmoticonID;
				myCommand.Parameters.Add("@PostAuthor", SqlDbType.NVarChar, 64).Value = entry.Username;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = entry.AuthorID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = entry.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = entry.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = entry.UserHostAddress;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = entry.IsSticky;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = entry.StickyDate;
				myCommand.Parameters.Add("@ApplicationPostType", SqlDbType.Int, 4).Value = entry.FileGalleryPostType;
                myCommand.Parameters.Add("@PostDisplayName",SqlDbType.NVarChar,64).Value = CSContext.Current.User.DisplayName;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = entry.IsApproved;

				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = entry.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Direction = ParameterDirection.Output;


				myConnection.Open();
				myCommand.ExecuteNonQuery();

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					entry.PostID = (int) myCommand.Parameters["@PostID"].Value;
					entry.ThreadID = (int) myCommand.Parameters["@ThreadID"].Value;
				} 
				catch {}

				if (entry.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}

		public override void CreateEntryData(Entry entry, PostAttachment entryData)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Add", connection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = entry.PostID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = entry.AuthorID;
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = entry.SectionID;
				myCommand.Parameters.Add("@Filename", SqlDbType.NVarChar, 256).Value = entryData.FileName;
				myCommand.Parameters.Add("@Content", SqlDbType.Image).Value = entryData.Content;
				myCommand.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = entryData.ContentType;
				myCommand.Parameters.Add("@ContentSize", SqlDbType.Int).Value = entryData.Length;
				myCommand.Parameters.Add("@IsRemote", SqlDbType.Bit).Value = entryData.IsRemote;
				if(entryData.IsImage)
				{
					myCommand.Parameters.Add("@Height", SqlDbType.Int).Value = entryData.Height;
					myCommand.Parameters.Add("@Width", SqlDbType.Int).Value = entryData.Width;
				}
				myCommand.Parameters.Add(this.SettingsIDParameter());

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
		}

		public override void DeleteEntryData(int postID)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_PostAttachment_Delete", connection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;

				connection.Open();
				myCommand.ExecuteNonQuery();
				connection.Close();
			}
		}

		public override void UpdateEntry(Entry entry, int editedByUserID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = entry.PostID;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = entry.Subject;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = entry.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = entry.FormattedBody;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = entry.EmoticonID;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = entry.IsLocked;
				myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = editedByUserID;
				myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = entry.EditNotes;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = entry.IsSticky;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = entry.StickyDate;
				myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = entry.IsApproved;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = entry.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override void DeleteEntry(int sectionID, int threadID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Thread_Delete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = sectionID;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int, 4).Value = threadID;
				myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int, 4).Value = CSContext.Current.UserID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override void DeletePost(int postID, int userID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_DeletePost", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int, 4).Value = postID;
				myCommand.Parameters.Add("@DeletedBy", SqlDbType.Int, 4).Value = userID;
				myCommand.Parameters.Add("@Reason", SqlDbType.NVarChar, 1024).Value = "Blogger Delete";
				myCommand.Parameters.Add("@DeleteChildPosts", SqlDbType.Bit).Value = true;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ThreadSet GetComments(FileGalleryThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".[files_comments_getComments]", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
					//command.Parameters.Add("@sqlPopulate", SqlDbType.NVarChar, 4000).Value = SqlGenerator.BuildGalleryThreadQuery(query, databaseOwner);
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = new FileThreadQueryBuilder(query, databaseOwner).BuildQuery();
					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludePageIndex", SqlDbType.Bit).Value = query.IncludePageIndex;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{

							while(dr.Read())
							{
								EntryComment comment = new EntryComment();
								CommonDataProvider.PopulatePostFromIDataReader(dr, comment);
								ts.Threads.Add(comment);
							}

							if(query.IncludePageIndex)
							{
								dr.NextResult();
								while(dr.Read())
									ts.PageIndex.Add(dr[0], dr[1]);
							}
							dr.Close();
							connection.Close();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}

					return ts;

				}
			}
		}

		public override ThreadSet GetEntries(FileGalleryThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".files_Entries_GetEntries", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = query.PageSize;
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = new FileThreadQueryBuilder(query, databaseOwner).BuildQuery();
					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					//If we want to display the list of categories for the post, we will need to 
					//use a dataset so we can join the child records
					if(query.IncludeCategories)
					{
						DataSet ds = new DataSet();
						SqlDataAdapter da = new SqlDataAdapter(command);

						//wait as long as possible to open the conn
						connection.Open();
						da.Fill(ds);
						connection.Close();

						//keep a referece to the relation since it is used in the GetChildRows look up anyway
						DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
						ds.Relations.Add(relation);
                        
						DataRowCollection posts = ds.Tables[0].Rows;
						foreach(DataRow dr in posts)
						{
							ts.Threads.Add(PopulateEntryFromIDataRow(dr, relation, true, true, true, true));
						}
						ds.Clear();
						ds.Dispose();

						ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

					}
					else
					{
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{
						
							while(dr.Read())
							{
								ts.Threads.Add(PopulateEntryFromIDataReader(dr, true, true, true, true));
							}

							//                            if(query.IncludeCategories)
							//                                dr.NextResult();

							dr.NextResult();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
					}

					return ts;

				}
			}
		}

		public override Entry GetEntry(int postID, int userID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".files_Entry_Get", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@TrackViews", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if (!dr.Read()) 
				{
					dr.Close();
					myConnection.Close();
					// we did not get back a post
					throw new CSException(CSExceptionType.PostNotFound, postID.ToString());
				}

				Entry e = PopulateEntryFromIDataReader(dr, false, true, true, true);

				dr.Close();
				myConnection.Close();

				// we have a post to work with  
				return e;
			}
		}

		public override int GetRandomPostID(int sectionID, int categoryID)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_GetRandomPostID", myConnection);
				int postID;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = categoryID;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				if(!dr.Read())
				{
					dr.Close();
					myConnection.Close();
					return -1;
				}

				postID = (int) dr["PostID"];

				dr.Close();
				myConnection.Close();

				// we have a post to work with  
				return postID;
			}
		}

		public override ArrayList GetComments(int postID, bool allowUnapproved)
		{
			using( SqlConnection myConnection = GetSqlConnection() )
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Posts_PostSet", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList comments = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value            = postID;
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value         = 0;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value          = 1000;
				myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value            = 0;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value         = CommunityServer.Components.SortOrder.Ascending;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value            = 0;
				myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@AllowUnapproved", SqlDbType.Bit).Value	= allowUnapproved;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				SqlDataReader reader = myCommand.ExecuteReader();

				// Get the results
				//
				while(reader.Read())
					if((int)reader["PostID"] != postID)
					{
						EntryComment comment = new EntryComment();
						CommonDataProvider.PopulatePostFromIDataReader(reader, comment);
						comments.Add( comment );
					}

				reader.Close();
				myConnection.Close();

				return comments;
			}
		}

		public override void UpdateComment(EntryComment comment, int userID, string editNotes)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_Update", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = comment.SectionID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = comment.PostID;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = comment.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = comment.IsLocked;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = comment.EmoticonID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = comment.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = comment.FormattedBody;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = comment.IsApproved;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = DateTime.Now;
				myCommand.Parameters.Add("@IsAnnouncement", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@EditedBy", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@EditNotes", SqlDbType.NText).Value = editNotes;

				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = comment.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				myConnection.Open();
				myCommand.ExecuteNonQuery();

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					comment.PostID = (int) myCommand.Parameters["@PostID"].Value;
				} 
				catch {}

				if (comment.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}

		public override void CreateComment(EntryComment comment)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Post_CreateUpdate", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = comment.SectionID;
				myCommand.Parameters.Add("@ThreadID", SqlDbType.Int).Value = comment.ThreadID;
				myCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = comment.ParentID;
				myCommand.Parameters.Add("@AllowDuplicatePosts", SqlDbType.Bit).Value = FileGalleryConfiguration.GetConfig().EnableDuplicatePosts;
				myCommand.Parameters.Add("@DuplicateIntervalInMinutes", SqlDbType.Int).Value = FileGalleryConfiguration.GetConfig().DuplicatePostIntervalInMinutes;
				myCommand.Parameters.Add("@Subject", SqlDbType.NVarChar, 256).Value = comment.Subject;
				myCommand.Parameters.Add("@IsLocked", SqlDbType.Bit).Value = comment.IsLocked;
				myCommand.Parameters.Add("@PostType", SqlDbType.Int).Value = comment.PostType;
				myCommand.Parameters.Add("@PostMedia", SqlDbType.Int).Value = comment.PostMedia;
				myCommand.Parameters.Add("@EmoticonID", SqlDbType.Int).Value = comment.EmoticonID;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = comment.User.UserID;
				myCommand.Parameters.Add("@Body", SqlDbType.NText).Value = comment.Body;
				myCommand.Parameters.Add("@FormattedBody", SqlDbType.NText).Value = comment.FormattedBody;
				myCommand.Parameters.Add("@UserHostAddress", SqlDbType.NVarChar, 32).Value = comment.UserHostAddress;
				myCommand.Parameters.Add("@IsApproved", SqlDbType.Bit).Value = comment.IsApproved;
				myCommand.Parameters.Add("@IsSticky", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@StickyDate", SqlDbType.DateTime).Value = DateTime.Now;
				myCommand.Parameters.Add("@UpdateSectionStatistics", SqlDbType.Bit).Value = false;
				myCommand.Parameters.Add("@ApplicationPostType", SqlDbType.Int, 4).Value = comment.FileGalleryPostType;

				myCommand.Parameters.Add(this.SettingsIDParameter());

				SerializerData data = comment.GetSerializerData();

				myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = data.Keys;
				myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = data.Values;

				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Direction = ParameterDirection.Output;

				myConnection.Open();
				myCommand.ExecuteNonQuery();

				// LN 5/27/04: try/catch added to get rid of exceptions
				try 
				{
					comment.PostID = (int) myCommand.Parameters["@PostID"].Value;
				} 
				catch {}

				if (comment.PostID == -1) 
				{
					myConnection.Close();
					throw new CSException(CSExceptionType.PostDuplicate);
				}

				myConnection.Close();
			}
		}

		public override ArrayList GetFileGalleryPermissions(int id)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Section_Permissions_Get", myConnection);
				ArrayList permissions = new ArrayList();
				SqlDataReader reader;

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int, 4).Value = id;
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.TinyInt).Value = ApplicationType.FileGallery;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				myConnection.Open();

				reader = myCommand.ExecuteReader();

				while(reader.Read()) 
				{
					FileGalleryPermission perm = new FileGalleryPermission();
					CommonDataProvider.PopulatePermissionFromIDataReader(perm, reader);
					permissions.Add(perm);
				}

				myConnection.Close();

				return permissions;
			}
		}

		public override bool ApprovePost(int postID, int userIDApprovedBy)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_ApprovePost", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@ApprovedBy", SqlDbType.Int).Value = userIDApprovedBy;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int iResult = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
				myConnection.Close();

				return iResult == 1;        // was the post previously approved?
			}
		}

		public override int GetEntrySortOrder(int folderID, int categoryID, int postID, EntriesSortBy sortBy)
		{
			// Create Instance of Connection and Command Object
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".files_Post_GetSortOrder", myConnection);

				StringBuilder sqlPopulateSelect = new StringBuilder("SELECT T.ThreadID ");
				StringBuilder fromClause        = new StringBuilder(" FROM " + databaseOwner + ".cs_Threads T ");
				StringBuilder whereClause       = new StringBuilder(" WHERE ");
				StringBuilder orderClause		= new StringBuilder(" ORDER BY ");

				#region Joins

				// Add joins
				fromClause.Append(" LEFT JOIN " + databaseOwner + ".cs_Posts P ON P.ThreadID = T.ThreadID ");
				if(categoryID != -1)
					fromClause.Append(" LEFT JOIN " + databaseOwner + ".cs_Posts_InCategories C ON C.PostID = P.PostID ");

				#endregion

				#region Where Clause

				// Find ColumnID
				whereClause.Append("T.SectionID = ");
				whereClause.Append(folderID);
				if(categoryID != -1)
				{
					whereClause.Append(" AND C.CategoryID = ");
					whereClause.Append(categoryID);
				}
				whereClause.Append(" AND P.PostID = P.ParentID");

				#endregion

				#region Order Clause

				switch(sortBy)
				{
					default:
					case EntriesSortBy.Subject:
						orderClause.Append( " P.Subject ASC " );
						break;
					case EntriesSortBy.Author:
						orderClause.Append( " T.PostAuthor ASC " );
						break;
					case EntriesSortBy.Comments:
						orderClause.Append( " T.TotalReplies ASC " );
						break;
					case EntriesSortBy.Views:
						orderClause.Append( " T.TotalViews ASC " );
						break;
					case EntriesSortBy.Rating:
						orderClause.Append( " case when T.TotalRatings > 0 then (convert(decimal,T.RatingSum) / convert(decimal,T.TotalRatings)) else 0 end ASC " );
						break;
				}

				#endregion

				// Build the SQL statements
				sqlPopulateSelect.Append(fromClause.ToString());
				sqlPopulateSelect.Append(whereClause.ToString());
				sqlPopulateSelect.Append(orderClause.ToString());

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = postID;
				myCommand.Parameters.Add("@sqlPopulate", SqlDbType.NVarChar, 4000).Value = sqlPopulateSelect.ToString();
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				myConnection.Open();
				int sortOrder = Convert.ToInt32(myCommand.ExecuteScalar().ToString());
				myConnection.Close();

				return sortOrder;
			}
		}

		public override PostSet SearchReindexPosts (int setsize, int settingsID) 
		{
			FileGalleryThreadQuery query = new FileGalleryThreadQuery();
			query.FirstPageOnly = true;
			query.PageSize = setsize;
			query.IncludeCategories = true;
			query.ApplicationPostType = FileGalleryPostType.File;
			query.OnlyApproved = true;
			query.SortBy = EntriesSortBy.PostDate;
			query.SortOrder = CommunityServer.Components.SortOrder.Descending;

			//looks like filegallery doesnt support a published flag at this time
			//query.PublishedFilter = EntryPublishedFilter.Published;
			
			query.IncludePageIndex = true;
			
			ThreadSet ts = SearchIndexPosts(settingsID, query);

			PostSet postSet = new PostSet();
			foreach(Entry entry in ts.Threads)
				postSet.Posts.Add(entry); 

			return postSet;
		}

		public override ThreadSet SearchIndexPosts (int setsize, FileGalleryThreadQuery query)
		{
			using( SqlConnection connection = GetSqlConnection() ) 
			{
				using(SqlCommand command = new SqlCommand(databaseOwner + ".[cs_shared_Threads_GetThreadSet]", connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@SectionID", SqlDbType.Int).Value = query.SectionID;
					command.Parameters.Add("@PageIndex", SqlDbType.Int, 4).Value = query.PageIndex;
					command.Parameters.Add("@PageSize", SqlDbType.Int, 4).Value = SQLHelper.GetSafeSqlInt(query.PageSize) ;
					//command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = SqlGenerator.BuildBlogThreadQuery(query,databaseOwner);

					//squeze in handling for published date and isapproved flags not found
					//in the FileThreadQueryBuilder

					string sqlindex = new FileThreadQueryBuilder(query,databaseOwner).BuildQuery();
                    string appTypeFilter = string.Format("S.ApplicationType={0} and S.IsSearchable=1", (int)ApplicationType.FileGallery); 
					sqlindex = sqlindex.Replace("where ", string.Format("where P.IsIndexed=0 and {0} and ", appTypeFilter))  ;
					
					command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = sqlindex;

					command.Parameters.Add("@UserID", SqlDbType.Int).Value = query.UserID;
					command.Parameters.Add("@IncludeCategories", SqlDbType.Bit).Value = query.IncludeCategories;
					command.Parameters.Add("@IncludePageIndex", SqlDbType.Bit).Value = query.IncludePageIndex;
					command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
					command.Parameters.Add(SettingsIDParameter());

					ThreadSet ts = new ThreadSet();

					// Execute the command
					
					//If we want to display the list of categories for the post, we will need to 
					//use a dataset so we can join the child records
					if(query.IncludeCategories)
					{
						DataSet ds = new DataSet();
						SqlDataAdapter da = new SqlDataAdapter(command);

						//wait as long as possible to open the conn
						connection.Open();
						da.Fill(ds);
						connection.Close();

						//keep a referece to the relation since it is used in the GetChildRows look up anyway
						DataRelation relation = new DataRelation("Categories",ds.Tables[0].Columns["PostID"],ds.Tables[1].Columns["PostID"],false);
						ds.Relations.Add(relation);
                        
						DataRowCollection posts = ds.Tables[0].Rows;
						foreach(DataRow dr in posts)
						{
							ts.Threads.Add(PopulateEntryFromIDataRow(dr,relation, true, true, true, false));
						}
   
						if(query.IncludePageIndex)
						{
							DataRowCollection pages = ds.Tables[2].Rows;
							foreach(DataRow dr in pages)
								ts.PageIndex.Add(dr[0], dr[1]);
						}
						
						ds.Clear();
						ds.Dispose();

						ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;

					}
					else
					{
						//No categories needed, so we can use a datareader.

						connection.Open();
						using(SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
						{
						
							while(dr.Read())
							{
								ts.Threads.Add(PopulateEntryFromIDataReader(dr, true, true, true, false));
							}

							if(query.IncludePageIndex)
							{
								dr.NextResult();
								while(dr.Read())
									ts.PageIndex.Add(dr[0], dr[1]);
							}

							dr.Close();
							connection.Close();
							ts.TotalRecords = (int) command.Parameters["@TotalRecords"].Value;
						}
					}

					return ts;

				}
			}
		}


		public override void LogDownload(int entryID, int userID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".files_Downloads_Log", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add parameters
				//
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@PostID", SqlDbType.Int).Value = entryID;

				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		public override ArrayList GetFoldersToModerate(int userID)
		{
			// Create Instance of Connection and Command Object
			//
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Moderate_Forums", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				ArrayList folders = new ArrayList();

				// Set parameters
				//
				myCommand.Parameters.Add(this.SettingsIDParameter());
				myCommand.Parameters.Add("@ApplicationType", SqlDbType.SmallInt).Value = ApplicationType.FileGallery;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;

				// Execute the command
				//
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				// Get the results
				//
				while (dr.Read())
				{
					Folder folder = new Folder();
					CommonDataProvider.PopulateSectionFromIDataRecord(dr, folder);
					folders.Add(folder);
				}

				dr.Close();
				myConnection.Close();

				return folders;
			}
		}

		public override PostSet GetEntriesToModerate(int sectionID, int pageIndex, int pageSize, int sortBy, int sortOrder, int userID, bool returnRecordCount) 
		{
			// Create Instance of Connection and Command Object
			//
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_forums_Moderate_PostSet", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				PostSet postSet = new PostSet();

				// Set parameters
				//
				myCommand.Parameters.Add("@SectionID", SqlDbType.Int).Value = sectionID;
				myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
				myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
				myCommand.Parameters.Add("@SortBy", SqlDbType.Int).Value = sortBy;
				myCommand.Parameters.Add("@SortOrder", SqlDbType.Int).Value = sortOrder;
				myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID;
				myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = returnRecordCount;
				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Execute the command
				//
				myConnection.Open();
				SqlDataReader dr = myCommand.ExecuteReader();

				// Get the results
				//
				while(dr.Read())
					postSet.Posts.Add( PopulateEntryFromIDataReader(dr, false, false, false, false));

				// Are we expecting more results?
				//
				if ((returnRecordCount) && (dr.NextResult()) )
					postSet.TotalRecords = (int) dr[0];

				dr.Close();
				myConnection.Close();

				return postSet;
			}
		}

	}
}
